package omm.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.joda.time.DateTime;

import omm.dao.DatabaseStrategy;
import omm.dao.OrderDAO;
import omm.domain.Company;
import omm.domain.Entity;
import omm.domain.Order;
import omm.domain.Quotation;
import omm.domain.Risk;

public class QuotationAndOrderAndRiskStrategy implements DatabaseStrategy {

	//private OrderDAO orderDAO;

	public QuotationAndOrderAndRiskStrategy(OrderDAO orderDAO) {
		//this.orderDAO = orderDAO;
	}

	@Override
	public PreparedStatement getStatement(Connection conn, DateTime beginning, DateTime ending) throws SQLException {
		Long lbeginning = beginning.getMillis()/1000;
		Long lending = ending.getMillis()/1000;
		PreparedStatement stmt = conn
				.prepareStatement("select c.id, c.subid, c.pedido_fk, p.nombre_cliente, c.riesgo_fk,c.compania_fk,"
						+ "c.comision_entidad,c.comision_aon, c.comision_porc,c.prima, c.estado, c.co_modif_user,"
						+ "c.co_create_user, c.co_specialist_user,c.ts, c.modif_ts,c.resolucion_fecha,c.premio,"
						+ "c.premio_actual, c.motivo_perdida,"
						+ "c.riesgo_fk, ifnull(r.desc_min,r.descripcion) as 'desc_min', "
						+ "r.descripcion as 'descripcion_r' " + "from pedido p "
						+ "LEFT JOIN cotizacion c ON (p.id = c.pedido_fk) "
						+ "LEFT JOIN compania cia ON (cia.id = c.compania_fk) "
						+ "LEFT JOIN riesgo r ON (r.id = c.riesgo_fk) "
						+ "LEFT JOIN entidad e ON (e.id = p.entidad_fk) "
						+ "LEFT JOIN sucursal s ON (p.sucursal_fk = s.id) "
						+ "LEFT JOIN region re ON (re.id = s.region_fk) "
						+ "LEFT JOIN usuario u ON (p.admin_fk = u.id) "
						+ "LEFT JOIN usuario u2 ON (p.modif_admin_fk = u2.id) "
						+ "LEFT JOIN usuario u3 ON (c.co_specialist_user = u3.id) "
						+ "where p.modif_ts between 1199152800 and 1325385728 "
						+ "ORDER BY p.id, c.riesgo_fk, c.subid asc, c.estado desc, c.premio asc, c.id asc");
		stmt.setLong(1, lbeginning);
		stmt.setLong(2, lending);
		return stmt;
	}

	@Override
	public Quotation makeQuotation(ResultSet cur) throws SQLException {
		Integer orderID = cur.getInt("pedido_fk");
		Order order = new Order(orderID,cur.getString("nombre_cliente"));

		Integer riskID = cur.getInt("riesgo_fk");
		Risk risk = null;
		if(riskID!=0)
			risk = new Risk(riskID, cur.getString("descripcion_r"), cur.getString("desc_min"));
		else
			risk = new Risk();
			
		Integer companyID = cur.getInt("compania_fk");
		Company company = (companyID == 0) ? null : new Company();

		Long creationUT = (long) cur.getInt("ts") * 1000;
		DateTime creationDate = (creationUT == 0) ? null : new DateTime(creationUT);

		Long modificationUT = (long) cur.getInt("modif_ts") * 1000;
		DateTime modificationDate = (modificationUT == 0) ? null : new DateTime(modificationUT);

		Long resolutionUT = (long) cur.getInt("resolucion_fecha") * 1000;
		DateTime resolutionDate = (resolutionUT == 0) ? null : new DateTime(resolutionUT);

		Quotation quotation = new Quotation(cur.getInt("subid"), order, risk, company,
				cur.getDouble("comision_entidad"), cur.getDouble("comision_aon"),
				cur.getDouble("comision_porc"), cur.getDouble("prima"), Quotation.State.fromCode(cur
						.getInt("estado")), cur.getString("co_modif_user"), cur.getString("co_create_user"),
				cur.getString("co_specialist_user"), creationDate, modificationDate, resolutionDate,
				cur.getDouble("premio"), cur.getDouble("premio_actual"), Quotation.LostReason.fromCode(cur
						.getInt("motivo_perdida")));
		quotation.setId(cur.getInt("id"));
		return quotation;
	}

}
